import pymysql
import pandas as pd
from sqlalchemy import null


class DBOperations:

    def __init__(self, host, user, password, database):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.connection = None
        cursor = null
        try:
            self.connection = pymysql.connect(host=self.host,
                                              user=self.user,
                                              password=self.password,
                                              database=self.database)
            self.cursor = self.connection.cursor(pymysql.cursors.DictCursor)

        except pymysql.Error as e:
            print(f"Error connecting to database: {e}")

        return None

    def insert(self, table_name, data_dict):

        try:
            cursor = self.connection.cursor()
            keys = ', '.join(data_dict.keys())
            values = ', '.join(['%s'] * len(data_dict))
            sql = f"INSERT INTO {table_name} ({keys}) VALUES ({values})"
            cursor.execute(sql, tuple(data_dict.values()))
            self.connection.commit()
        except pymysql.Error as e:
            print(f"Error inserting data: {e}")
            self.connection.rollback()

    def savetoexcel(self, tablename, condition, filename):
        cursor = self.connection.cursor()
        query = f"SELECT * FROM {tablename} WHERE {condition}"
        cursor.execute(query)

        # 获取列名
        column_names = [desc[0] for desc in cursor.description]

        # 获取数据
        data = cursor.fetchall()

        # 将数据转换为 DataFrame
        df = pd.DataFrame(data, columns=column_names)
        df.to_excel(filename, index=False)

    def insert_dict_array_to_mysql(self, table_name, dict_array):
        try:
            cursor = self.connection.cursor()
            # 构建插入语句的前缀
            columns = ', '.join(dict_array[0].keys())
            placeholders = ', '.join(['%s'] * len(dict_array[0]))
            sql = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"

            # 准备数据
            data = []
            for item in dict_array:
                values = [item[key] for key in item]
                data.append(tuple(values))

            cursor.executemany(sql, data)
            self.connection.commit()
            print(f'executemany sql={sql}')
        except pymysql.Error as e:
            print(f"Error occurred during insertion: {e}")
            self.connection.rollback()

    def update(self, table_name, data_dict, condition):
        try:
            cursor = self.connection.cursor()
            set_values = ', '.join([f"{key} = %s" for key in data_dict.keys()])
            sql = f"UPDATE {table_name} SET {set_values} WHERE {condition}"
            cursor.execute(sql, tuple(data_dict.values()))
            print(sql)
            self.connection.commit()
        except pymysql.Error as e:
            print(f"Error updating data: {e}")
            self.connection.rollback()

    def execsql(self, sql):
        try:
            cursor = self.connection.cursor()

            cursor.execute(sql)
            print(sql)
            self.connection.commit()
        except pymysql.Error as e:
            print(f"Error updating data: {e}")
            self.connection.rollback()

    def delete(self, table_name, condition):
        if not self.connect():
            return
        try:
            cursor = self.connection.cursor()
            sql = f"DELETE FROM {table_name} WHERE {condition}"
            cursor.execute(sql)
            self.connection.commit()
        except pymysql.Error as e:
            print(f"Error deleting data: {e}")
            self.connection.rollback()

    def query(self, table_name, condition=None):
        try:

            if condition:
                sql = f"SELECT * FROM {table_name} WHERE {condition}"
            else:
                sql = f"SELECT * FROM {table_name}"
            cursor = self.connection.cursor(pymysql.cursors.DictCursor)
            cursor.execute(sql)
            results = cursor.fetchall()
            return results
        except pymysql.Error as e:
            print(f"Error querying data: {e}")
            return None
